-- This script was generated by the Schema Diff utility in pgAdmin 4
-- For the circular dependencies, the order in which Schema Diff writes the objects is not very sophisticated
-- and may require manual changes to the script to ensure changes are applied in the correct order.
-- Please report an issue for any failure with the reproduction steps.
CREATE OR REPLACE VIEW public.user_metrics
  WITH (security_invoker) AS
  SELECT request.user_id,
    min(request.created_at) AS first_active,
    max(request.created_at) AS last_active,
    count(request.id) AS total_requests,
    count(request.id)::double precision / count(DISTINCT date_trunc('day'::text, request.created_at))::double precision AS average_requests_per_day_active,
    avg(((response.body ->> 'usage'::text)::json->> 'total_tokens'::text)::integer) AS average_tokens_per_request
    FROM request
      LEFT JOIN response ON response.request = request.id
  GROUP BY request.user_id;

GRANT ALL ON TABLE public.user_metrics TO service_role;
GRANT ALL ON TABLE public.user_metrics TO authenticated;
GRANT ALL ON TABLE public.user_metrics TO anon;
